package m.common.model.util;

import m.common.model.*;
import m.common.model.config.ModelConfig;
import m.common.model.type.FieldType;
import m.system.RuntimeData;
import m.system.db.DBManager;
import m.system.db.DataSet;
import m.system.exception.MException;
import m.system.lang.HtmlBodyContent;
import m.system.util.*;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import java.io.*;
import java.sql.SQLException;
import java.util.Map;

public class ModelUtil {
	public static <T extends Model> void initModelTable(Class<T> clazz) throws MException {
		TableMeta tableMeta=ModelConfig.getTableMeta(clazz);
		if(!tableMeta.autoCreate()) return;
		Map<String,FieldMeta> fieldMap=ModelConfig.getFieldMetaMap(clazz);
		Map<String,LinkTableMeta> linkTableMap=ModelConfig.getLinkTableMetaMap(clazz);
		//自动创建或补列 （创建会插入初始化数据）
		String tableSchema=ModelConfig.getTableSchema();
		if(!StringUtil.isSpace(tableSchema)){
			if(tableMeta.isView()){//视图表
				creatView(tableMeta);
			}else{
				if(!isHasTable(tableSchema,tableMeta.name())){
					creatTable(clazz);
				}else{
					for(String key : fieldMap.keySet()){
						FieldMeta field=fieldMap.get(key);
						if(!isHasField(tableSchema, tableMeta.name(), field.name())){
							String desc=field.description();
							if(!StringUtil.isSpace(field.dictType())) desc+=" 字典（"+field.dictType()+"）";
							if(!StringUtil.isSpace(field.dictOption())) desc+=" 选项（"+field.dictOption()+"）";
							addField2Table(tableSchema,tableMeta.name(),field.name(),getDatabaseType(field),desc,field.notnull(),false);
						}
					}
					for(String key : linkTableMap.keySet()){
						LinkTableMeta field=linkTableMap.get(key);
						if(!isHasField(tableSchema, tableMeta.name(), field.name())){
							addField2Table(tableSchema,tableMeta.name(),field.name(),"VARCHAR(20)",field.description(),field.notnull(),true);
						}
					}
				}
			}
		}
	}
	/**
	 * 创建或更新视图
	 *  tableMeta
	 *  MException 
	 *  SQLException
	 */
	private static void creatView(TableMeta tableMeta) throws MException{
		StringBuilder sql=new StringBuilder();
		String viewSql;
		if(StringUtil.isSpace(tableMeta.viewSql())){
			viewSql=getViewSql(tableMeta.name());
		}else{
			viewSql=tableMeta.viewSql();
		}
		if(tableMeta.autoCreate()) {
			if (StringUtil.isSpace(viewSql)) {
				throw new MException(ModelUtil.class, "没有找到" + tableMeta.name() + "的视图!");
			}
			sql.append("CREATE OR REPLACE VIEW ").append(tableMeta.name()).append(" AS ").append(viewSql);
			try {
				DBManager.executeUpdate(sql.toString());
			} catch (SQLException e) {
				throw new MException(ModelUtil.class, "创建视图时SQL出错!" + e.getMessage());
			}
		}
	}
	private static String getViewSql(String tableName) throws MException{
		InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("config/dbviewsql.xml");
		DocumentBuilderFactory domfac=DocumentBuilderFactory.newInstance();
		DocumentBuilder dombuilder;
		try {
			dombuilder = domfac.newDocumentBuilder();
			Document doc=dombuilder.parse(is);
			Element root=doc.getDocumentElement();
			NodeList sqlList=root.getChildNodes();
            for (int i = 0; i < sqlList.getLength(); i++) {
                Node sql = sqlList.item(i);
                if (null == sql.getAttributes()) continue;
                Node attr = sql.getAttributes().getNamedItem("tableName");
                if (null != attr && tableName.equals(attr.getTextContent())) {
                    return sql.getTextContent();
                }
            }
        } catch (Exception e) {
			throw new MException(ModelUtil.class,"获取视图SQL出错!"+e.getMessage());
		}
		return null;
	}
	private static boolean isHasTable(String tableSchema,String tableName) throws MException {
		try {
			if(tableName.contains(".")){
				String[] arr=tableName.split("\\.");
				tableSchema=arr[0];
				tableName=arr[1];
			}
			DataSet ds=DBManager.executeQuery("select count(*) num from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA=? and TABLE_NAME=?",new String[]{tableSchema,tableName});
			return ds.size() > 0 && ds.get(Long.class, 0, "num") > 0;
		} catch (SQLException e) {
			throw new MException(ModelUtil.class,"查询是否存在表时SQL出错!"+e.getMessage());
		}
	}
	private static <T extends Model> void creatTable(Class<T> clazz) throws MException{
		TableMeta tableMeta=ModelConfig.getTableMeta(clazz);
		Map<String,FieldMeta> fieldMap=ModelConfig.getFieldMetaMap(clazz);
		Map<String,LinkTableMeta> linkTableMap=ModelConfig.getLinkTableMetaMap(clazz);
		StringBuilder sql=new StringBuilder(100);
		sql.append("CREATE TABLE ").append(tableMeta.name()).append(" (");
		sql.append(tableMeta.oidField());
		if(tableMeta.oidIncrement()){
			sql.append(" BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键', ");
		}else{
			sql.append(" VARCHAR(50) NOT NULL COMMENT 'oid主键', ");
		}
		for(String key : fieldMap.keySet()){
			FieldMeta field=fieldMap.get(key);
			String desc=field.description();
			if(!StringUtil.isSpace(field.dictType())) desc+=" 字典（"+field.dictType()+"）";
			if(!StringUtil.isSpace(field.dictOption())) desc+=" 选项（"+field.dictOption()+"）";
			sql.append(field.name()).append(" ").append(getDatabaseType(field)).append(field.notnull()?" NOT NULL":" NULL").append("  COMMENT '").append(desc).append("',");
		}
		for(String key : linkTableMap.keySet()){
			LinkTableMeta field=linkTableMap.get(key);
			if(field.name().equals(tableMeta.oidField())) continue;//排除oid字段创建，说明该字段主键和关联表一致
			TableMeta linkTable=ModelConfig.getTableMeta(field.table());
			sql.append(field.name()).append(linkTable.oidIncrement()?" BIGINT ":" VARCHAR(50) ").append(field.notnull()?" NOT NULL":" NULL").append(" COMMENT '").append(field.description()).append("',");
		}
		sql.append("PRIMARY KEY (").append(tableMeta.oidField()).append(") ");
		sql.append(" ) ").append("COMMENT='").append(tableMeta.description()).append("' CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci");
		try {
			DBManager.executeUpdate(sql.toString());
			executeInitSql(tableMeta.name());
		} catch (SQLException e) {
			throw new MException(ModelUtil.class,"创建数据库时SQL出错!"+e.getMessage());
		}
	}
	private static void executeInitSql(String tableName) throws MException{
		InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("config/dbinitsql.xml");
		DocumentBuilderFactory domfac=DocumentBuilderFactory.newInstance();
		DocumentBuilder dombuilder;
		try {
			dombuilder = domfac.newDocumentBuilder();
			Document doc=dombuilder.parse(is);
			Element root=doc.getDocumentElement();
			NodeList sqlList=root.getChildNodes();
			if(sqlList!=null){
				for(int i=0;i<sqlList.getLength();i++){
					Node sql=sqlList.item(i);
					if(null==sql.getAttributes()) continue;
					Node attr=sql.getAttributes().getNamedItem("tableName");
					if(null!=attr&&tableName.equals(attr.getTextContent())){
						String[] sqls=sql.getTextContent().split(";");
						for(String s : sqls){
							if(StringUtil.notSpace(s)){
								DBManager.executeUpdate(s);
							}
						}
//						DBManager.batchUpdate(sqls);
						break;
					}
				}
			}
		} catch (Exception e) {
			throw new MException(ModelUtil.class,"初始化数据出错!"+e.getMessage());
		}
	}
	private static boolean isHasField(String tableSchema,String tableName,String fieldName) throws MException{
		try {
			if(tableName.contains(".")){
				String[] arr=tableName.split("\\.");
				tableSchema=arr[0];
				tableName=arr[1];
			}
			DataSet ds=DBManager.executeQuery("DESCRIBE " + tableSchema + "." + tableName + " " + fieldName);
			return ds.size() > 0;
		} catch (SQLException e) {
			throw new MException(ModelUtil.class,"查询是否存在字段时SQL出错!"+e.getMessage());
		}
	}
	private static void addField2Table(String tableSchema,String tableName,String fieldName,String type,String description,boolean notnull,boolean index) throws MException{
		if(tableName.contains(".")){
			String[] arr=tableName.split("\\.");
			tableSchema=arr[0];
			tableName=arr[1];
		}
		StringBuilder sql=new StringBuilder("ALTER TABLE ").append(tableSchema).append(".").append(tableName)
				.append(" ADD COLUMN ").append(fieldName).append(" ").append(type).append(notnull?" NOT NULL":" NULL").append(" COMMENT '").append(description).append("'");
		if(index){
			sql.append(",ADD INDEX ind_").append(fieldName).append(" (").append(fieldName).append(" ASC)");
		}
		try {
			DBManager.executeUpdate(sql.toString());
		} catch (SQLException e) {
			throw new MException(ModelUtil.class,"添加字段时SQL出错!"+e.getMessage());
		}
	}
	private static String getDatabaseType(FieldMeta field){
		boolean isDef= !StringUtil.isSpace(field.defaultValue());
		StringBuilder sb=new StringBuilder();
		if(FieldType.STRING.equals(field.type())){
			if(StringUtil.isSpace(field.dbType())) sb.append("VARCHAR(").append(field.length()).append(")");
			else sb.append(field.dbType());
			if(isDef) sb.append(" default '").append(field.defaultValue()).append("'");
		}else if(FieldType.INT.equals(field.type())){
			if(StringUtil.isSpace(field.dbType())) sb.append("INT");
			else sb.append(field.dbType());
			if(isDef) sb.append(" default ").append(field.defaultValue());
		}else if(FieldType.DOUBLE.equals(field.type())){
			if(StringUtil.isSpace(field.dbType())) sb.append("DOUBLE");
			else sb.append(field.dbType());
			if(isDef) sb.append(" default ").append(field.defaultValue());
		}else if(FieldType.DATE.equals(field.type())){
			if(StringUtil.isSpace(field.dbType())) sb.append("DATETIME");
			else sb.append(field.dbType());
			if(isDef) sb.append(" default ").append(field.defaultValue());
		}else{
			if(StringUtil.isSpace(field.dbType())) sb.append("CHAR(1)");
			else sb.append(field.dbType());
			if(isDef) sb.append(" default '").append(field.defaultValue()).append("'");
		}
		return sb.toString();
	}
	/**
	 * 转换对象为jsonMessage
	 *  <T>
	 *  a
	 *  model
	 *
	 *  MException
	 */
	@SuppressWarnings("unchecked")
	public static <T extends Model> JSONMessage toJSONMessage(String a,T model) throws MException{
		String prefix=StringUtil.isSpace(a)?"":(a+".");
		JSONMessage message=new JSONMessage();
		Class<T> clazz=(Class<T>) model.getClass();
		Map<String,FieldMeta> fieldMap=ModelConfig.getFieldMetaMap(clazz);
		Map<String,LinkTableMeta> linkTableMap=ModelConfig.getLinkTableMetaMap(clazz);
		Map<String, QueryFieldMeta> queryFieldMap=ModelConfig.getQueryFieldMetaMap(clazz);
		if(null!=model.getOid()){
			message.push(prefix+"oid", model.getOid());
		}else {
			message.push(prefix+"oid", null);
		}
		for(String key : fieldMap.keySet()){
			Object obj=ClassUtil.getFieldValue(model, key);
			if(null!=obj){
				message.push(prefix+key,obj);
			}else {
				if(fieldMap.get(key).type()==FieldType.STRING
						&&ClassUtil.getDeclaredField(clazz,key).getType().isArray()){
					message.push(prefix+key,new String[]{});
				}else{
					message.push(prefix+key,null);
				}
			}
		}
		for(String key : queryFieldMap.keySet()){
			Object obj=ClassUtil.getFieldValue(model, key);
			if(null!=obj){
				message.push(prefix+key,obj);
			}else {
				if(ClassUtil.getDeclaredField(clazz,key).getType().isArray()){
					message.push(prefix+key,new String[]{});
				}else{
					message.push(prefix+key,null);
				}
			}
		}
		for(String key : linkTableMap.keySet()){
			Model bean=(Model) ClassUtil.getFieldValue(model, key);
			if(null!=bean){
				message.push(prefix+key, toJSONMessage("", bean));
			}else {
				message.push(prefix+key, new HtmlBodyContent("{}"));
			}
		}
		return message;
	}
	public static <T extends Model> File serialize(T model) throws IOException{
		File file;
		FileOutputStream fos=null;
		ObjectOutputStream oos=null;
		try {
			String path= RuntimeData.getPublicWeb() + RuntimeData.getFilePath() + "serialize/" + GenerateID.tempKey() + ".txt";
			file=FileUtil.getFile(path);
			fos = new FileOutputStream(file);
			oos = new ObjectOutputStream(fos);
			oos.writeObject(model);
			oos.flush();
		} finally {
			if(null!=oos) oos.close();
			if(null!=fos) fos.close();
		}
		return file;
	}
	@SuppressWarnings("unchecked")
	public static <T extends Model> T deserialize(File file) throws IOException, ClassNotFoundException{
		T model;
		try (FileInputStream fis = new FileInputStream(file); ObjectInputStream ois = new ObjectInputStream(fis)) {
			model = (T) ois.readObject();
		}
		return model;
	}
}
